In order to analyze data more effectively, people always need to arrange excel worksheet data information into meaningful order. You can sort the excel text data into alphabetical order as well as sort numeric data into numerical order. This post will share a method with you about how to sort data information in Excel worksheet with C#, VB.NET in both horizontal and vertical directions.
Sort data information in Excel worksheet with C#,VB.NET
This method can be finished by three steps. I use an Excel component Spire.XLS for .NET to be my tool. Spire.XLS supports C#, VB.NET, ASP.NET and MVC. Please look at the below procedure.
Step1. Create a new project.
Sort data information in Excel worksheet with C#,VB.NET
This method can be finished by three steps. I use an Excel component Spire.XLS for .NET to be my tool. Spire.XLS supports C#, VB.NET, ASP.NET and MVC. Please look at the below procedure.
Step1. Create a new project.
- Create a new project in Windows Forms Application.
- Set the Target framework of the project in Properties to be .NET Framework 2 or above
- Add a button in Form1, the default name is “button1”
- Add Spire.Xls dll as reference and add its namespace at the top of the method. The Version of Spire.XLS is 6.5 or above.
Freely Download Spire.XLS
Step2. Sort data information in Excel worksheet
1. Create a workbook and load an Excel file from system
C# Code:
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\sort data.xls");
Worksheet worksheet = workbook.Worksheets[0];
VB.NET Code:
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\e-iceblue\Spire.XLS\Demos\Data\sort data.xls")
Dim worksheet As Worksheet = workbook.Worksheets(0)
2. Sort data information
In this step, you can select any column that you need to sort and sort range. I use the method” workbook.DataSorter.SortColumns.Add(x, OrderBy.Descending);” to sort excel data.”x” can be “0”or “1” or “…” ‘0” means the first column and “1” the second column.
Step2. Sort data information in Excel worksheet
1. Create a workbook and load an Excel file from system
C# Code:
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\sort data.xls");
Worksheet worksheet = workbook.Worksheets[0];
VB.NET Code:
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\e-iceblue\Spire.XLS\Demos\Data\sort data.xls")
Dim worksheet As Worksheet = workbook.Worksheets(0)
2. Sort data information
In this step, you can select any column that you need to sort and sort range. I use the method” workbook.DataSorter.SortColumns.Add(x, OrderBy.Descending);” to sort excel data.”x” can be “0”or “1” or “…” ‘0” means the first column and “1” the second column.
C# Code:
//append the sort column index and order by attributes
workbook.DataSorter.SortColumns.Add(4, OrderBy.Descending);
//set the range to sort.
workbook.DataSorter.Sort(worksheet["A1:E19"]);
VB.NET Code:
'append the sort column index and order by attributes
workbook.DataSorter.SortColumns.Add(4, OrderBy.Descending)
'set the range to sort.
workbook.DataSorter.Sort(worksheet("A1:E19"))
Step3. Save and launch the file
C# Code:
workbook.SaveToFile(@"test.xls",ExcelVersion.Version97to2003);
ExcelDocViewer(@"test.xls");
private void ExcelDocViewer(string fileName)
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch { }
}
VB.NET Code:
workbook.SaveToFile("test.xls", ExcelVersion.Version97to2003)
ExcelDocViewer("test.xls")
Private Sub ExcelDocViewer(fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Press F5 to debug the project, click button1 to preview
Preview
workbook.DataSorter.Sort(worksheet["A1:E19"]);
VB.NET Code:
'append the sort column index and order by attributes
workbook.DataSorter.SortColumns.Add(4, OrderBy.Descending)
'set the range to sort.
workbook.DataSorter.Sort(worksheet("A1:E19"))
Step3. Save and launch the file
C# Code:
workbook.SaveToFile(@"test.xls",ExcelVersion.Version97to2003);
ExcelDocViewer(@"test.xls");
private void ExcelDocViewer(string fileName)
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch { }
}
VB.NET Code:
workbook.SaveToFile("test.xls", ExcelVersion.Version97to2003)
ExcelDocViewer("test.xls")
Private Sub ExcelDocViewer(fileName As String)
Try
System.Diagnostics.Process.Start(fileName)
Catch
End Try
End Sub
Press F5 to debug the project, click button1 to preview
Preview